"""
数据分析模块的数据库操作
"""
from datetime import datetime
from typing import List, Optional, Tuple, Any

from app.models.analytics_models import MeterFreeze, AlarmStats, DeviceInventoryRow
from app.schemas.analytics_schemas import AlarmTopDeviceItem
from common.database import DatabaseService
from common.exceptions import BizException, ErrorCode
from common.logger import create_logger

logger = create_logger("analytics_crud")


# ---------- 用量统计相关操作 ----------
async def get_meter_freeze_data(
		db: DatabaseService,
		meter_codes: List[str],
		start_date: datetime,
		end_date: datetime
) -> List[MeterFreeze]:
	"""
	获取电表冻结数据
	"""
	if not meter_codes:
		return []

	placeholders = ','.join(['%s'] * len(meter_codes))
	sql = f"""
        SELECT id, serial_no, freeze_day, total_kwh, create_at
        FROM dc_meter_freeze
        WHERE freeze_day BETWEEN %s AND %s
          AND serial_no IN ({placeholders})
        ORDER BY freeze_day
    """
	params = [start_date.date(), end_date.date()] + meter_codes

	try:
		rows = db.get_all(sql, params)
		return [MeterFreeze(**row) for row in rows]
	except Exception as e:
		logger.exception("查询电表冻结数据失败: %s", e)
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="查询电表冻结数据失败") from e


async def get_meter_codes_by_station(db: DatabaseService, station_id: int) -> List[str]:
	"""
	根据台区ID获取该台区下所有电表设备序列号
	"""
	sql = """
        SELECT m.serial_no as serial_no
        FROM dev_meter m
        JOIN dev_rtu r ON r.id = m.rtu_id
        JOIN dev_box b ON b.id = r.box_id
        JOIN dev_station s ON s.id = b.station_id
        WHERE s.id = %s AND m.deleted = 0
    """
	try:
		rows = db.get_all(sql, (station_id,))
		return [row["serial_no"] for row in rows]
	except Exception as e:
		logger.exception("查询台区下电表设备序列号失败: %s", e)
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="查询台区下电表编码失败") from e


async def get_max_power_data(
		db: DatabaseService,
		meter_codes: List[str],
		start_date: datetime,
		end_date: datetime
) -> Optional[Tuple[float, datetime]]:
	"""
	获取指定时间范围内的最大功率及时间
	"""
	if not meter_codes:
		return None

	placeholders = ','.join(['%s'] * len(meter_codes))
	sql = f"""
        SELECT
        t.watt * m.ct_ratio * m.pt_ratio AS primary_watt,
        t.ts
		FROM
			dc_meter_telemetry t
			INNER JOIN dev_meter m ON t.meter_code = m.serial_no
		WHERE
			ts BETWEEN %s AND %s
			AND  m.serial_no IN ({placeholders})
		ORDER BY
			primary_watt DESC
		LIMIT 1
    """
	params = [start_date, end_date] + meter_codes

	try:
		row = db.get_one(sql, params)
		if row and row["primary_watt"] is not None:
			return float(row["primary_watt"]), row["ts"]
		return None
	except Exception as e:
		logger.exception("查询最大功率数据失败: %s", e)
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="查询最大功率数据失败") from e


# ---------- 告警统计相关操作 ----------
async def get_alarm_stats_by_status(
		db: DatabaseService,
		start_date: datetime,
		end_date: datetime
) -> List[AlarmStats]:
	"""
	按状态统计告警
	"""
	sql = """
        SELECT status, COUNT(*) as count
        FROM am_alarm
        WHERE raised_at BETWEEN %s AND %s
        GROUP BY status
    """
	try:
		rows = db.get_all(sql, (start_date, end_date))
		return [AlarmStats(status=row["status"], count=row["count"], alarm_type=None, raised_at=None)
				for row in rows]
	except Exception as e:
		logger.exception("按状态统计告警失败: %s", e)
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="按状态统计告警失败") from e


async def get_alarm_stats_by_type(
		db: DatabaseService,
		start_date: datetime,
		end_date: datetime
) -> List[AlarmStats]:
	"""
	按类型统计告警
	"""
	sql = """
        SELECT alarm_type, COUNT(*) as count
        FROM am_alarm
        WHERE raised_at BETWEEN %s AND %s
        GROUP BY alarm_type
    """
	try:
		rows = db.get_all(sql, (start_date, end_date))
		return [AlarmStats(alarm_type=row["alarm_type"], count=row["count"], status=None, raised_at=None)
				for row in rows]
	except Exception as e:
		logger.exception("按类型统计告警失败: %s", e)
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="按类型统计告警失败") from e


async def get_alarm_trend_stats(
		db: DatabaseService,
		start_date: datetime,
		end_date: datetime
) -> List[AlarmStats]:
	"""
	按天统计告警趋势
	"""
	sql = """
        SELECT DATE(raised_at) as raised_at, COUNT(*) as count
        FROM am_alarm
        WHERE raised_at BETWEEN %s AND %s
        GROUP BY DATE(raised_at)
        ORDER BY raised_at
    """
	try:
		rows = db.get_all(sql, (start_date, end_date))
		return [AlarmStats(raised_at=row["raised_at"], count=row["count"], status=None, alarm_type=None)
				for row in rows]
	except Exception as e:
		logger.exception("按天统计告警趋势失败: %s", e)
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="按天统计告警趋势失败") from e


async def get_top_alarm_devices(
		db: DatabaseService,
		start_date: datetime,
		end_date: datetime,
		limit: int = 5
) -> List[AlarmTopDeviceItem]:
	"""
	获取告警最多的设备排名
	"""
	sql = """
        SELECT meter_code, COUNT(*) as count
        FROM am_alarm
        WHERE raised_at BETWEEN %s AND %s
        GROUP BY meter_code
        ORDER BY count DESC
        LIMIT %s
    """
	try:
		rows = db.get_all(sql, (start_date, end_date, limit))
		return [AlarmTopDeviceItem(meter_code=row["meter_code"], count=row["count"])
				for row in rows]
	except Exception as e:
		logger.exception("获取告警设备排名失败: %s", e)
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="获取告警设备排名失败") from e


# ---------- 设备资产统计相关操作 ----------
async def get_device_inventory_stats(
		db: DatabaseService,
		province_code: str | None = None,
		city_code: str | None = None,
		area_code: str | None = None,
) -> List[DeviceInventoryRow]:
	where_parts: list[str] = []
	params: list[str | None] = []

	if province_code is not None:
		where_parts.append("s.province_code = %s")
		params.append(province_code)
	if city_code is not None:
		where_parts.append("s.city_code = %s")
		params.append(city_code)
	if area_code is not None:
		where_parts.append("s.area_code = %s")
		params.append(area_code)

	sql = f"""
        SELECT
            COUNT(DISTINCT s.id)  AS total_stations,
            COUNT(DISTINCT b.id)  AS total_boxes,
            COUNT(DISTINCT r.id) AS total_rtus,
            COUNT(DISTINCT m.id)  AS total_meters,
            m.phase,
            m.meter_type,
            m.status,
            COUNT(*)              AS count
        FROM dev_station s
        JOIN dev_box   b ON b.station_id = s.id
        JOIN dev_rtu   r ON r.box_id     = b.id
        JOIN dev_meter m ON m.rtu_id     = r.id
        WHERE s.deleted = 0
          AND b.deleted = 0
          AND r.deleted = 0
          AND m.deleted = 0
          {"".join(" AND " + w for w in where_parts)}
        GROUP BY m.phase, m.meter_type, m.status
    """
	rows = db.get_all(sql, tuple(params))
	return [DeviceInventoryRow.model_validate(row) for row in rows]


# ---------- 设备用电量统计相关操作 ----------
async def get_device_daily_consumption(
		db: DatabaseService,
		serial_no: Optional[str],
		station_id: Optional[int],
		start_date: datetime,
		end_date: datetime
) -> List[dict]:
	"""
	根据设备序列号或台区ID获取每日用电量数据
	
	参数:
	- serial_no: 设备序列号（可选）
	- station_id: 台区ID（可选）
	- start_date: 开始日期
	- end_date: 结束日期
	
	返回:
	- List[dict]: 包含 freeze_day 和 total_kwh 的字典列表
	"""
	# 构建查询条件
	where_conditions = []
	params = []
	if start_date and end_date:
		where_conditions = ["freeze_day BETWEEN %s AND %s"]
		params: List[Any] = [start_date.date(), end_date.date()]

	if serial_no:
		# 按设备序列号查询
		where_conditions.append("serial_no = %s")
		params.append(serial_no)
	if station_id:
		where_conditions.append("""
			serial_no IN (
				SELECT serial_no 
				FROM v_all_device 
				WHERE deleted = 0 AND device_type = 'RTU' AND station_id = %s 
			)
		""")
		params.append(station_id)

	where_clause = " AND ".join(where_conditions)

	sql = f"""
		SELECT 
			freeze_day,
			SUM(daily_kwh) as total_kwh
		FROM dc_meter_freeze
		WHERE {where_clause if where_clause else '1=1'}
		GROUP BY freeze_day
		HAVING SUM(daily_kwh) > 0   
		ORDER BY freeze_day
	"""
	try:
		rows = db.get_all(sql, tuple(params))
		return rows
	except Exception as e:
		logger.exception("查询设备每日用电量数据失败: %s", e)
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="查询设备用电量数据失败") from e
